package br.com.rtp.connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectionFactory {
	
	private Connection conn;
	private Statement stm;
	
	public ConnectionFactory() throws ClassNotFoundException, SQLException{

		Class.forName("org.sqlite.JDBC");
		
		this.conn = DriverManager.getConnection("jdbc:sqlite:tarefa.db");
		
		this.stm = this.conn.createStatement();
	}
	
	public Connection getConnection(){
		return conn;
	}
	
	public void initDB() {
	try {
		
		this.stm = this.conn.createStatement();
		
		//Inicializa o DB
		this.stm.executeUpdate("DROP TABLE if exists tarefa");
		this.stm.executeUpdate("create table tarefa (id integer not null primary key autoincrement," +
	     		"titulo varchar2(255) not null , " + "dtPrazo varchar2(30) not null ," +
	     		"descricao varchar2(255), " + "responsavel varchar2(255) not null," +
	     		"urgente boolean not null , " + "status varchar2(30) default 'Aberto', " +
	     		"dtConclusao varchar2(30)," + "descSolucao varchar2(255))");	     		
		
		//Cria a trigger para a data da conclusao
		this.stm.executeUpdate("CREATE TRIGGER UPD_STATUS AFTER UPDATE OF dtConclusao ON " +
	     		"TAREFA BEGIN UPDATE TAREFA SET STATUS='Fechado' WHERE " +
	     		"ROWID=NEW.ROWID; END;");
		
		stm.close();
		conn.close();
		System.out.println("BD inicializado e tabela criada!");
		
	} catch (SQLException e) {
		e.printStackTrace();
	}
	}
	
}
